package com.epam.edu.dao;

import com.epam.edu.db.ConnectionPool;
import com.epam.edu.entity.Subject;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class DAOSubject {
    public static Subject selectById(Connection connection,int id) {
        Subject subject = null;
//        ConnectionPool pool = null;
//        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
//            ConnectionPool.init();
//            pool = ConnectionPool.getInstance();
//            connection = pool.takeConnection();
            String command = "SELECT name FROM Subjects WHERE id=? AND isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                subject = new Subject(id, resultSet.getString(1));
            }
//            pool.releaseConnection(connection);
//            ConnectionPool.dispose();
        } catch (SQLException e) {
            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return subject;
    }

    public static Subject selectByName(Connection connection,String name) {
        Subject subject = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            String command = "SELECT id FROM Subjects WHERE name=? AND isDelete='false'";
            statement = connection.prepareStatement(command);
            statement.setString(1, name);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                subject = new Subject(resultSet.getInt(1), name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return subject;
    }

    public static List<Subject> selectAll(Connection connection) {
        List<Subject> subjects = new LinkedList<Subject>();
//        ConnectionPool pool = null;
//        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
//            ConnectionPool.init();
//            pool = ConnectionPool.getInstance();
//            connection = pool.takeConnection();
            String command = "SELECT id,name FROM Subjects WHERE isDelete='false'";
            statement = connection.prepareStatement(command);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Subject subject = new Subject(resultSet.getInt(1), resultSet.getString(2));
                subjects.add(subject);
            }
//            pool.releaseConnection(connection);
//            ConnectionPool.dispose();
        } catch (SQLException e) {
            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (resultSet != null) resultSet.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return subjects;
    }
}
